﻿using System;
using System.Data;
using System.Data.Common;
using ChinaDynastyTours.Helpers;
using ChinaDynastyTours.Models;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Text;
using System.Threading;
using System.Collections.Generic;

namespace ChinaDynastyTours.Controls
{
    class TeamGroupCtrl
    {
        #region 团有关数据获取

        /// <summary>
        /// 检测是否存在同名团名
        /// </summary>
        /// <param name="tourgroupName"></param>
        /// <returns>true存在，false不存在</returns>
        internal static bool CheckExistSameTourgroupName(string tourgroupId, string tourgroupName)
        {
            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;

            dbCommand = db.GetSqlStringCommand(string.Format("SELECT TourGroupName FROM orgt_tourgroup WHERE TourGroupName='{0}' AND TourGroupID <> '{1}' AND LEFT(TourGroupID,4) = LEFT('{1}',4) AND TourgroupState <> '{2}'", tourgroupName.Trim(), tourgroupId.Trim(), TeamStateEnum.删除));
            object obj = db.ExecuteScalar(dbCommand);

            return obj != null;
        }

        /// <summary>
        /// 检测是否存在已申请费用
        /// </summary>
        /// <param name="tourGroupScheduleID"></param>
        /// <param name="TypeBelong"></param>
        /// <returns>true存在，false不存在</returns>
        internal static bool CheckExistPayApply(string tourGroupScheduleID, string typeBelong)
        {
            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;

            if (typeBelong == "")
                dbCommand = db.GetSqlStringCommand(string.Format("SELECT PayDetailID FROM fin_PayDetail,fee_Expense WHERE fee_Expense.TourGroupScheduleID = {0} AND fin_PayDetail.ExpenseID = fee_Expense.ExpenseID", tourGroupScheduleID));
            else
                dbCommand = db.GetSqlStringCommand(string.Format("SELECT PayDetailID FROM fin_PayDetail,fee_Expense WHERE fee_Expense.TourGroupScheduleID = {0} AND fee_Expense.TypeBelong = '{1}' AND fin_PayDetail.ExpenseID = fee_Expense.ExpenseID", tourGroupScheduleID, typeBelong));
            
            object obj = db.ExecuteScalar(dbCommand);

            return obj != null;
        }

        /// <summary>
        /// 根据团号获取组团人A
        /// </summary>
        /// <param name="tourgroupid"></param>
        /// <returns></returns>
        public static string GetTeamGroupOwnerAByTourgroupID(string tourgroupid)
        {

            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
            string sql = string.Format("SELECT OwnerA FROM orgt_tourgroup WHERE tourgroupid='{0}'", tourgroupid);
            dbCommand = db.GetSqlStringCommand(sql);

            return db.ExecuteScalar(dbCommand).ToString();
        }
        /// <summary>
        ///  根据团号获取组团A的信息
        /// </summary>
        /// <returns></returns>
        public static DataTable GetOwnerADataByTourgroupID(string tourgroupid)
        {

            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
            string sql = string.Format(@"SELECT aspnet_UserInfo.*  FROM aspnet_UserInfo,orgt_TourGroup WHERE aspnet_UserInfo.[Name] = orgt_TourGroup.OwnerA and TourgroupID='{0}'", tourgroupid);
            dbCommand = db.GetSqlStringCommand(sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        /// <summary>
        /// 根据团号获取团名
        /// </summary>
        /// <returns></returns>
        public static string GetTeamGroupNameById(string tourgroupid)
        {

            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
            string sql = string.Format("SELECT TourgroupName FROM orgt_tourgroup where tourgroupid='{0}'", tourgroupid);
            dbCommand = db.GetSqlStringCommand(sql);

            object obj = db.ExecuteScalar(dbCommand);
            if (obj == DBNull.Value || obj == null)
                return "";
            else
                return obj.ToString();
        }

        /// <summary>
        /// 根据团号获取部门Email
        /// </summary>
        /// <returns></returns>
        public static string GetDepartmentEmailById(string tourgroupid)
        {

            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
            string sql = string.Format("SELECT bas_Department.MailServer FROM bas_Department,orgt_tourgroup where orgt_tourgroup.tourgroupid='{0}' and orgt_tourgroup.DepartmentID = bas_Department.DepartmentID", tourgroupid);
            dbCommand = db.GetSqlStringCommand(sql);

            return db.ExecuteScalar(dbCommand).ToString();
        }

        public static TeamStateEnum GetTourGroupState(string tourGroupID)
        {
            if (tourGroupID == "") return TeamStateEnum.全部;

            Database db = DatabaseFactory.CreateDatabase();

            string sql = string.Format("SELECT TourgroupState FROM orgt_tourgroup WHERE TourgroupID='{0}'",tourGroupID);
            DbCommand dbCommand = db.GetSqlStringCommand(sql);

            object obj = db.ExecuteScalar(dbCommand);
            if(obj==DBNull.Value || obj==null)
                return TeamStateEnum.全部;
            else
                return EnumHelper.Team_Str2Enum(obj.ToString(), true);
        }
        /// <summary>
        /// 获取指定团的房间备注信息
        /// </summary>
        /// <param name="tourGroupID"></param>
        /// <returns></returns>
        public static string GetTeamGroupRoomRemark(string tourGroupID)
        {

            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
            string sql = "SELECT RoomRemark FROM orgt_tourgroup WHERE TourGroupID = @tourGroupID";
            dbCommand = db.GetSqlStringCommand(sql);

            db.AddParameter(dbCommand, "@tourGroupID", DbType.String, ParameterDirection.Input, "", DataRowVersion.Default, tourGroupID);

            return db.ExecuteScalar(dbCommand).ToString();

        }
        /// <summary>
        /// 根据指定计划号获取团的国家代码
        /// </summary>
        /// <param name="tourgroupId"></param>
        /// <returns></returns>
        public static string GetCountryNameForTeamGroup(string tourgroupId)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand;
            string sql = "SELECT Country FROM orgt_tourgroup WHERE TourGroupID = @tourGroupID";
            dbCommand = db.GetSqlStringCommand(sql);

            db.AddParameter(dbCommand, "@tourGroupID", DbType.String, ParameterDirection.Input, "", DataRowVersion.Default, tourgroupId);
            return db.ExecuteScalar(dbCommand).ToString();
        }
        /// <summary>
        /// 获取系统中所有团的团号/计划号
        /// </summary>
        /// <returns></returns>
        public static DataSet GetAllIDForTeamGroup()
        {

            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
            string sql = "SELECT tourgroupid FROM orgt_tourgroup";
            dbCommand = db.GetSqlStringCommand(sql);

            return db.ExecuteDataSet(dbCommand);
        }
        public static DataSet GetAllIDForTeamGroup(string loginProviderId)
        {

            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
            //string sql = string.Format(@"SELECT distinct t.TourGroupID from orgt_TourGroup t,Sch_ProvidersPlanSchedule p WHERE t.TourgroupID=p.TourgroupID AND p.OwnerProviderID={3} AND  (TourgroupState = '{0}' OR TourgroupState = '{1}' OR TourgroupState = '{2}')", TeamStateEnum.分配, TeamStateEnum.审核, TeamStateEnum.终结, loginProviderId);
            string sql = string.Format(@"SELECT distinct t.TourGroupID from orgt_TourGroup t,orgt_TourGroupSchedule s WHERE t.TourgroupID=s.TourgroupID AND s.TravelAgencyID={3} AND  (TourgroupState = '{0}' OR TourgroupState = '{1}' OR TourgroupState = '{2}')", TeamStateEnum.分配, TeamStateEnum.审核, TeamStateEnum.终结, loginProviderId);
            dbCommand = db.GetSqlStringCommand(sql);

            return db.ExecuteDataSet(dbCommand);
        }
        /// <summary>
        /// 根据给定的计划号，数组获取团信息
        /// </summary>
        /// <param name="tourgroupIds"></param>
        /// <returns></returns>
        internal static DataTable GetTeamGroupData(string[] tourgroupIds)
        {
            StringBuilder szCondition = new StringBuilder();
            if (tourgroupIds.Length > 0)
                szCondition.Append(" WHERE ");
            else
                return null;

            Database db = DatabaseFactory.CreateDatabase();
            StringBuilder formatSql = new StringBuilder("SELECT * FROM orgt_tourgroup ");

            for(int i = 0; i < tourgroupIds.Length; i++)
            {
                szCondition.AppendFormat(" TourgroupID='{0}' ", tourgroupIds[i]);
                if(i!=tourgroupIds.Length-1)
                    szCondition.Append(" OR ");
            }

            if (szCondition.Length > 0)
                formatSql.Append(szCondition);

            DbCommand dbCommand = db.GetSqlStringCommand(formatSql.ToString());
            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        /// <summary>
        ///  根据给定的客户名称检索客户所在团信息
        /// </summary>
        /// <param name="guestName"></param>
        /// <returns></returns>
        internal static DataTable GetTeamGroupData(string guestName, int providerLoginID)
        {
            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
            string sql = string.Format(@"SELECT t.* FROM orgt_TourGroup t,orgt_TourGroupSchedule s 
                                         WHERE  t.TourgroupID in (SELECT tourgroupid FROM orgt_guestlist WHERE [Name] like '%'+@GuestName+'%')
	                                     AND t.TourgroupID=s.TourgroupID AND s.TravelAgencyID=@ProviderID AND (t.TourgroupState=@TourgroupState1 OR t.TourgroupState=@TourgroupState2 OR t.TourgroupState=@TourgroupState3 OR t.TourgroupState=@TourgroupState4) ");
            dbCommand = db.GetSqlStringCommand(sql);
            db.AddParameter(dbCommand, "@TourgroupState1", DbType.String, ParameterDirection.Input, "", DataRowVersion.Default, TeamStateEnum.成行);
            db.AddParameter(dbCommand, "@TourgroupState2", DbType.String, ParameterDirection.Input, "", DataRowVersion.Default, TeamStateEnum.审核);
            db.AddParameter(dbCommand, "@TourgroupState3", DbType.String, ParameterDirection.Input, "", DataRowVersion.Default, TeamStateEnum.终结);
            db.AddParameter(dbCommand, "@TourgroupState4", DbType.String, ParameterDirection.Input, "", DataRowVersion.Default, TeamStateEnum.分配);
            db.AddParameter(dbCommand, "@ProviderID", DbType.Int32, ParameterDirection.Input, "", DataRowVersion.Default, providerLoginID);
            db.AddParameter(dbCommand, "@GuestName", DbType.String, ParameterDirection.Input, "", DataRowVersion.Default, guestName);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        
        /// <summary>
        /// 根据条件获取团数据
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <param name="teamstate"></param>
        /// <param name="username">我的团（当前登录用户）</param>
        /// <param name="departmentId"></param>
        /// <returns></returns>
        public static DataTable GetTeamGroupData(DateTime start, DateTime end, string loginProviderId, string ownerAB)
        {

            Database db = DatabaseFactory.CreateDatabase();
            StringBuilder szCondition = new StringBuilder();

            if (loginProviderId != "")//我的团条件，根据当前登录人判定
                szCondition.AppendFormat("s.TravelAgencyID={0}", loginProviderId);
            else
                return null;
            if (szCondition.Length != 0)//按着组团人
            {
                if (ownerAB != "")
                    szCondition.AppendFormat(" AND (OwnerA='{0}' OR OwnerB='{0}')", ownerAB);
            }
            else
            {
                if (ownerAB != "")
                    szCondition.AppendFormat(" (OwnerA='{0}' OR OwnerB='{0}')", ownerAB);
            }

            String Sql = string.Format(@"SELECT t.* from orgt_TourGroup t,orgt_TourGroupSchedule s WHERE t.TourgroupID=s.TourgroupID AND (TourgroupState = '{0}' OR TourgroupState = '{1}' OR TourgroupState = '{2}') AND StartDate >='{3}' AND StartDate<='{4}'", TeamStateEnum.分配, TeamStateEnum.审核, TeamStateEnum.终结, start.ToString("yyyy-MM-dd"), end.ToString("yyyy-MM-dd"));


            if (szCondition.Length != 0)
                Sql += " AND " + szCondition.AppendFormat(" ORDER BY StartDate");
            else
                Sql += " ORDER BY StartDate";

            DbCommand dbCommand = db.GetSqlStringCommand(Sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }

        /// <summary>
        /// 根据条件获取团数据
        /// </summary>
        /// <param name="hasGuide">false,全部，true,无导游</param>
        /// <param name="tourgroupId"></param>
        /// <param name="dateTime"></param>
        /// <returns></returns>
        internal static DataTable GetTeamGroupData(string tourGroupId, string dateMonth,string loginProviderId)//,string endDate)
        {
            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
            string szCondition = "";

            if (tourGroupId != "")
                szCondition = "t.TourGroupID='" + tourGroupId + "'";

            /*
                描述：通过条件过滤出属于当前登录供应商的团
                        1、通过大行程中TravelAgencyID确定所属‘登录’供应商的团
                        2、通过导游安排表进一步过滤属于登录供应商的团，通过导游安排表中OwnerProviderID找到属于登录供应商的已安排的团或未安排为NULL的团
                        注：个人理解Where条件过滤过程中，括号外的条件与括号内两个or条件是分别进行组合过滤的。
                        其中g.OwnerProviderID IS NULL语句原本是g.CiceroniName IS NULL，效果一样，为了美观采用OwnerProviderID
            */
            //          string Sql = string.Format(@"SELECT t.TourgroupID, t.TourgroupName, t.StartDate, t.EndDate, t.OwnerA, t.StayOver, t.AdultNum, t.ChildNum, g.CiceroniName FROM orgt_TourGroup t LEFT JOIN cic_GuideArrange g ON t.TourgroupID = g.TourgroupID
//                                         WHERE DATEDIFF(month,t.StartDate,'{0}')=0  AND HasGuide=1 AND t.TourgroupState<>'{1}'AND t.TourgroupState<>'{2}' AND t.TourgroupState<>'{3}'", dateMonth, TeamStateEnum.取消, TeamStateEnum.删除, TeamStateEnum.计划);
//            string Sql = string.Format(@"SELECT DISTINCT t.TourgroupID, t.TourgroupName, t.StartDate, t.EndDate, t.OwnerA, t.StayOver, t.AdultNum, t.ChildNum, g.CiceroniName 
//                                         FROM orgt_TourGroup t LEFT JOIN cic_GuideArrange g ON t.TourgroupID = g.TourgroupID,orgt_TourGroupSchedule s   
//                                         WHERE  t.tourgroupid=s.tourgroupid AND s.TravelAgencyID={4} AND (g.ownerproviderid='{4}' OR not exists(SELECT a.TourgroupID FROM cic_GuideArrange a WHERE a.OwnerProviderID='{4}')) AND
//                                                DATEDIFF(month,t.StartDate,'{0}')=0  AND HasGuide=1 AND (t.TourgroupState='{1}' OR t.TourgroupState='{2}' OR t.TourgroupState='{3}')", dateMonth,TeamStateEnum.分配, TeamStateEnum.审核, TeamStateEnum.终结, loginProviderId);
            string Sql = string.Format(@"SELECT DISTINCT t.HasGuide,t.TourgroupID, t.TourgroupName, t.StartDate, t.EndDate, t.OwnerA, t.StayOver, t.AdultNum, t.ChildNum, case when g.OwnerProviderID<>'{4}' then NULL else g.CiceroniName end as CiceroniName 
                                         FROM orgt_TourGroup t LEFT JOIN cic_GuideArrange g ON t.TourgroupID = g.TourgroupID,orgt_TourGroupSchedule s   
                                         WHERE  t.tourgroupid=s.tourgroupid AND s.TravelAgencyID={4} AND (g.ownerproviderid='{4}' OR t.TourgroupID not in (SELECT distinct a.TourgroupID FROM cic_GuideArrange a WHERE a.OwnerProviderID='{4}')) AND
                                                 HasGuide=1 AND (t.TourgroupState='{1}' OR t.TourgroupState='{2}' OR t.TourgroupState='{3}')   
                                                 AND DATEDIFF(month,t.StartDate,'{0}')=0 ", dateMonth, TeamStateEnum.分配, TeamStateEnum.审核, TeamStateEnum.终结, loginProviderId);
            if (szCondition != "")
                Sql += "  AND " + szCondition;

            dbCommand = db.GetSqlStringCommand(Sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }


        public static DataTable GetTeamGroupRowData(string tourGroupID)
        {

            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
            string sql = "SELECT * FROM orgt_tourgroup WHERE TourGroupID = @tourGroupID";
            dbCommand = db.GetSqlStringCommand(sql);

            db.AddParameter(dbCommand, "@tourGroupID", DbType.String, ParameterDirection.Input, "", DataRowVersion.Default, tourGroupID);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;

        }

        public static DataTable GetTeamGroupRowDataforHotelorder(string tourGroupID)
        {

            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
            string sql = "SELECT TourgroupName,(AdultNum+ChildNum) as GuestNum ,AccompanyNum,OwnerA,OwnerB FROM orgt_tourgroup WHERE TourGroupID = @tourGroupID";
            dbCommand = db.GetSqlStringCommand(sql);

            db.AddParameter(dbCommand, "@tourGroupID", DbType.String, ParameterDirection.Input, "", DataRowVersion.Default, tourGroupID);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;

        }


        internal static DataTable GetTrainDataForVoucher(string tourGroupId)
        {

            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;

            string Sql = string.Format(@"SELECT [TourGroupScheduleID],[TravelAgencyID],[Traffic],[Date],[Time], [StayOver], 
                                        [StartCity], [DestinationCity],[Seat],[TicketType] 
                                        FROM [orgt_TourGroupSchedule] WHERE TourgroupID='{0}'and TrafficType='{1}'and (Journeytype = '{2}'or Journeytype = '{3}')", tourGroupId, TrafficTypeEnum.火车, JourneyTypeEnum.离,JourneyTypeEnum.离境);

            dbCommand = db.GetSqlStringCommand(Sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }


        /// <summary>
        /// 获取最后生成的‘计划号’
        /// </summary>
        /// <returns></returns>
        public static string GetLastTourgroupID(string yy)
        {
            Database db = DatabaseFactory.CreateDatabase();
            object obj = db.ExecuteScalar(CommandType.Text, string.Format("SELECT MAX(TourgroupID) FROM orgt_TourGroup WHERE tourgroupid like '{0}%'", yy));
            if (obj != null)
                return obj.ToString();
            else
                return "";
        }

        /// <summary>
        /// 获取第一条行程ID
        /// </summary>
        /// <returns></returns>
        public static int GetFirstTourgroupScheduleID(string tourgroupID)
        {
            Database db = DatabaseFactory.CreateDatabase();
            object obj = db.ExecuteScalar(CommandType.Text, string.Format("SELECT top 1 [TourGroupScheduleID] FROM orgt_TourGroupSchedule WHERE TourgroupID ='{0}'", tourgroupID));
            if (obj != null)
                return int.Parse(obj.ToString());
            else
                return -1;
        }

        /// <summary>
        ///  获取指定团的备注信息
        /// </summary>
        /// <param name="tourGroupID"></param>
        /// <returns></returns>
        public static string GetTourGroupScheduleRemark(string tourGroupID)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format("SELECT [Remark] FROM orgt_TourGroup WHERE TourgroupID='{0}'", tourGroupID);

            return db.ExecuteScalar(CommandType.Text, sql).ToString();
        }
        /// <summary>
        ///  获取指定团的本地备注信息
        /// </summary>
        /// <param name="tourGroupID"></param>
        /// <returns></returns>
        public static string GetTourGroupScheduleLocalRemark(string tourGroupID)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format("SELECT [LocalRemark] FROM orgt_TourGroup WHERE TourgroupID='{0}'", tourGroupID);

            return db.ExecuteScalar(CommandType.Text, sql).ToString();
        }

        /// <summary>
        ///  获取指定团的导游信息
        /// </summary>
        /// <param name="tourGroupID"></param>
        /// <returns></returns>
        public static string GetTourGroupScheduleAccompanyNames(string tourGroupID)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format("SELECT [AccompanyNames] FROM orgt_TourGroup WHERE TourgroupID='{0}'", tourGroupID);

            return db.ExecuteScalar(CommandType.Text, sql).ToString();
        }

        /// <summary>
        /// 获取财务报表终结数据
        /// </summary>
        /// <returns></returns>
        internal static DataTable GetFinanceReportFinalData(string startDateFrom, string startDateTo, string finalTimeFrom, string finalTimeTo, string departId, string ownerA, List<TeamStateEnum> states)
        {
            Database db = DatabaseFactory.CreateDatabase();
            StringBuilder szCondition = new StringBuilder();
            StringBuilder formatSql = new StringBuilder();

            formatSql.Append(@"SELECT [TourgroupID], [TourgroupName], [TourgroupState], 
                                      [SaleTotalCost], [SaleTotalPrice],([SaleTotalPrice]- [SaleTotalCost]) as SaleMaori,
                                      [BudgeTotalCost], [BudgeTotalPrice],([BudgeTotalPrice]- [BudgeTotalCost]) as BudgeMaori, 
                                      [RealTotalCost], [RealTotalPrice],([RealTotalPrice]- [RealTotalCost]) as RealMaori 
                                      FROM orgt_TourGroup ");

            if (startDateFrom != "")
                szCondition.AppendFormat(" StartDate>='{0}' ", startDateFrom);

            if (szCondition.Length>0)
            {
                if (startDateTo != "")
                    szCondition.AppendFormat(" AND StartDate<='{0}' ", startDateTo);
            }
            else
            {
                if (startDateTo != "")
                    szCondition.AppendFormat(" StartDate<='{0}' ", startDateTo);
            }

            if (szCondition.Length > 0)
            {
                if (finalTimeFrom != "")
                    szCondition.AppendFormat(" AND FinalTime>='{0}' ", finalTimeFrom);
            }
            else
            {
                if (finalTimeFrom != "")
                    szCondition.AppendFormat(" FinalTime>='{0}' ", finalTimeFrom);
            }

            if (szCondition.Length > 0)
            {
                if (finalTimeTo != "")
                    szCondition.AppendFormat(" AND FinalTime<='{0}' ", finalTimeTo);
            }
            else
            {
                if (finalTimeTo != "")
                    szCondition.AppendFormat(" FinalTime<='{0}' ", finalTimeTo);
            }

            if (szCondition.Length > 0)
            {
                if (departId != "")
                    szCondition.AppendFormat(" AND DepartmentID={0} ", departId);
            }
            else
            {
                if (departId != "")
                    szCondition.AppendFormat(" DepartmentID={0} ", departId);
            }

            if (szCondition.Length > 0)
            {
                if (ownerA != "")
                    szCondition.AppendFormat(" AND OwnerA='{0}' ", ownerA);
            }
            else
            {
                if (ownerA != "")
                    szCondition.AppendFormat(" OwnerA='{0}' ", ownerA);
            }
            //拼接第1段Select语句
            if (states.Count > 0)
                szCondition.Append(" AND (");

            for (int i = 0; i < states.Count; i++)
            {
                szCondition.AppendFormat(" TourgroupState='{0}'", states[i]);

                if (i != states.Count - 1)
                    szCondition.Append(" OR ");
                else
                    szCondition.Append(")");
            }

            if (szCondition.Length > 0)
                formatSql.Append(" WHERE " + szCondition);

            DbCommand dbCommand = db.GetSqlStringCommand(formatSql.ToString());

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        #endregion

        #region 团有关数据增、删、改

        /// <summary>
        /// 根据指定的团号拷贝团数据行，生成新的团数据
        /// </summary>
        /// <param name="oldTourGroupId"></param>
        /// <param name="startDate"></param>
        /// <param name="endDate"></param>
        /// <param name="newTourgroupId"></param>
        /// <param name="newTourgroupName"></param>
        /// <returns></returns>
//        internal static int CopyTourGroup(string oldTourGroupId, DateTime startDate, DateTime endDate, string newTourgroupId,string newTourgroupName)
//        {
//            Database db = DatabaseFactory.CreateDatabase();
//            string sql = string.Format(@"INSERT INTO orgt_TourGroup 
//                                            ([TourgroupID], [TourgroupName], [TourgroupState], [AdultNum], [ChildNum], [StartDate], [EndDate], [OwnerA], [OwnerB],  [BudgeTotalCost], [BudgeTotalPrice], [Guesttype], [Servicetype], [Tourgrouptype], [Country], [CountryCode], [CustomerName], [CustomerCode], [StayOver], [AccompanyNum], [AccompanyNames], [HasGuide], [BuildGroupDate], [Remark], [RoomRemark], [LocalRemark], [DepartmentID],[Currency],[ExchangeRate],[CurrencyAmount],[SaleTotalCost],[SaleTotalPrice],[RealTotalCost],[RealTotalPrice],[SaleCost],[SalePrice],[PriceMethod])
//                                        SELECT 
//                                            '{0}', '{1}',  '{2}', [AdultNum], [ChildNum], '{3}', '{4}', [OwnerA], [OwnerB], 0, 0,  [Guesttype], [Servicetype], [Tourgrouptype], [Country], [CountryCode], [CustomerName], [CustomerCode], [StayOver], [AccompanyNum], [AccompanyNames], [HasGuide], [BuildGroupDate], [Remark], [RoomRemark], [LocalRemark], {5},[Currency],[ExchangeRate],[CurrencyAmount],[SaleTotalCost],[SaleTotalPrice],0,0,[SaleCost],[SalePrice],[PriceMethod]
//                                         FROM orgt_TourGroup WHERE TourgroupID='{6}'",
//                                            newTourgroupId,
//                                            newTourgroupName,
//                                            TeamStateEnum.计划,
//                                            startDate.ToString("yyyy-MM-dd"),
//                                            endDate.ToString("yyyy-MM-dd"),
//                                            int.Parse(UserInfoCtrl.GetUserInfoForDepartmentID(Thread.CurrentPrincipal.Identity.Name)),
//                                            oldTourGroupId);
//            return db.ExecuteNonQuery(CommandType.Text, sql);
//        }

        /// <summary>
        /// 创建新团
        /// </summary>
        /// <param name="tourGroupData"></param>
        /// <returns></returns>
        public static int CreateTourGroup(TourGroupEntity tourGroupData)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"INSERT INTO orgt_TourGroup ([TourgroupID], [TourgroupName], [TourgroupState],[AdultNum], [ChildNum], [StartDate], [EndDate],
                                        [BuildGroupDate],[OwnerA],[BudgeTotalCost],[BudgeTotalPrice],[RealTotalCost],[RealTotalPrice],[StayOver],[AccompanyNum],
                                        [HasGuide],[DepartmentID],[Currency],[ExchangeRate],[CurrencyAmount],[SaleTotalCost],[SaleTotalPrice],[SaleCost],[SalePrice],[PriceMethod]) 
                                        VALUES('{0}', '{1}','{2}', {3}, {4},'{5}','{6}','{7}','{8}',{9},{10},{11},{12},{13},{14},{15},{16},'{17}',{18},{19},{20},{21},{22},{23},'{24}')", 
                                        tourGroupData.TourgroupID,
                                        tourGroupData.TourgroupName,
                                        tourGroupData.TourgroupState,
                                        tourGroupData.AdultNum,
                                        tourGroupData.ChildNum,
                                        tourGroupData.StartDate,
                                        tourGroupData.EndDate,
                                        tourGroupData.BuildGroupDate,
                                        tourGroupData.OwnerA,
                                        tourGroupData.BudgeTotalCost,
                                        tourGroupData.BudgeTotalPrice,
                                        tourGroupData.RealTotalCost,
                                        tourGroupData.RealTotalPrice,
                                        tourGroupData.StayOver,
                                        tourGroupData.AccompanyNum,
                                        tourGroupData.HasGuide,
                                        tourGroupData.DepartmentID,
                                        tourGroupData.Currency,
                                        tourGroupData.ExchangeRate,
                                        tourGroupData.CurrencyAmount,
                                        tourGroupData.SaleTotalCost,
                                        tourGroupData.SaleTotalPrice,
                                        tourGroupData.SaleCost,
                                        tourGroupData.SalePrice,
                                        tourGroupData.PriceMethod);
            return db.ExecuteNonQuery(CommandType.Text,sql);
        }
        public static int DeleteTourGroup(string tourgroupID)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format("Delete from orgt_TourGroup WHERE TourgroupID='{0}'", tourgroupID);
            return db.ExecuteNonQuery(CommandType.Text, sql);
        }
        /// <summary>
        /// 更新团行数据的计调操作状态,0、试图打开但检测到没有本地计划信息需拷贝时，置为0； 1、以首次打开为依据，将大计划的本地行程信息拷贝过来，状态置为1；2、手动方式，在本地行程计划中添加一个check框，来确定是否团已经处理过了，true=2，false=1
        /// </summary>
        /// <param name="tourGroupId"></param>
        /// <param name="localTourgroupRowState"></param>
        /// <returns></returns>
        public static int UpdateTourGroupLocalTourgroupRowState(string tourGroupId, int localTourgroupRowState)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"UPDATE orgt_TourGroup SET LocalTourgroupRowState='{0}' WHERE TourgroupID='{1}'", localTourgroupRowState, tourGroupId);
            DbCommand dbCommand = db.GetSqlStringCommand(sql);

            return db.ExecuteNonQuery(dbCommand);
        }
        /// <summary>
        /// 更新团行数据的‘操作’操作状态,0、预留1、预留、2手动方式，在本地行程计划中添加一个check框，来确定是否团已经处理过了，true=2，false=1
        /// </summary>
        /// <param name="tourGroupId"></param>
        /// <param name="localTourgroupRowState"></param>
        /// <returns></returns>
        public static int UpdateTourGroupTourgroupRowState(string tourGroupId, int tourgroupRowState)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"UPDATE orgt_TourGroup SET TourgroupRowState='{0}' WHERE TourgroupID='{1}'", tourgroupRowState, tourGroupId);
            DbCommand dbCommand = db.GetSqlStringCommand(sql);

            return db.ExecuteNonQuery(dbCommand);
        }
        /// <summary>
        /// 更新团中全陪人名
        /// </summary>
        /// <param name="tourGroupId">团ID</param>
        /// <param name="accompanyNames">全陪人名，多个用‘|’分割</param>
        /// <returns></returns>
        public static int UpdateTourGroupAccompanyNames(string tourGroupId, string accompanyNames)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"UPDATE orgt_TourGroup SET AccompanyNames='{0}' WHERE TourgroupID='{1}'", accompanyNames, tourGroupId);
            DbCommand dbCommand = db.GetSqlStringCommand(sql);

            return db.ExecuteNonQuery(dbCommand);

        }
        /// <summary>
        /// 更新团的‘宾馆备注信息’字段
        /// </summary>
        /// <param name="tourGroupId"></param>
        /// <param name="state"></param>
        /// <returns></returns>
        public static int UpdateTourGroupRoomRemark(string tourGroupId, string roomRemark)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"UPDATE orgt_TourGroup SET RoomRemark='{0}' WHERE TourgroupID='{1}'", roomRemark, tourGroupId);
            DbCommand dbCommand = db.GetSqlStringCommand(sql);

            return db.ExecuteNonQuery(dbCommand);
        }
        /// <summary>
        /// 更新团的‘本地行程计划的备注’字段
        /// </summary>
        /// <param name="tourGroupId"></param>
        /// <param name="state"></param>
        /// <returns></returns>
        public static int UpdateTourGroupLocalRemark(string tourGroupId, string localRemark)
        {
            Database db = DatabaseFactory.CreateDatabase();
            //string sql = string.Format(@"UPDATE orgt_TourGroup SET LocalRemark='{0}' WHERE TourgroupID='{1}'", localRemark, tourGroupId);
            //DbCommand dbCommand = db.GetSqlStringCommand(sql);


            string sql = string.Format(@"UPDATE orgt_TourGroup SET [LocalRemark]=@Remark WHERE TourgroupID=@TourgroupID");
            DbCommand dbCommand = db.GetSqlStringCommand(sql);

            db.AddInParameter(dbCommand, "Remark", DbType.String, localRemark);
            db.AddInParameter(dbCommand, "TourgroupID", DbType.String, tourGroupId);

            return db.ExecuteNonQuery(dbCommand);
        }

        public static int UpdateTourGroupState(string tourGroupId, TeamStateEnum state)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql="";
            if(state== TeamStateEnum.审核)
                sql = string.Format(@"UPDATE orgt_TourGroup SET TourgroupState='{0}',[AuditUserName]='{1}',[AuditTime]='{2}' WHERE TourgroupID='{3}'",
                                        state,
                                        UserInfoCtrl.GetNameByUserName(Thread.CurrentPrincipal.Identity.Name),
                                        DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"),
                                        tourGroupId);
            if (state == TeamStateEnum.终结)
                sql = string.Format(@"UPDATE orgt_TourGroup SET TourgroupState='{0}',[FinalUserName]='{1}',[FinalTime]='{2}' WHERE TourgroupID='{3}'",
                                        state,
                                        UserInfoCtrl.GetNameByUserName(Thread.CurrentPrincipal.Identity.Name),
                                        DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"),
                                        tourGroupId);
            else//成行、计划、取消、全部、删除状态处理
                sql = string.Format(@"UPDATE orgt_TourGroup SET TourgroupState='{0}' WHERE TourgroupID='{1}'",state,tourGroupId);
            
            DbCommand dbCommand = db.GetSqlStringCommand(sql);
            
            return db.ExecuteNonQuery(dbCommand);
        }
        /// <summary>
        /// 根据团Id更新‘预算成本、预算价格’
        /// </summary>
        /// <param name="tourGroupId"></param>
        /// <param name="budgeTotalCost"></param>
        /// <param name="budgeTotalPrice"></param>
        /// <returns></returns>
        public static int UpdateTourGroupSale(string tourGroupId, decimal saleTotalCost, decimal saleTotalPrice,string currency,decimal exchangeRate,decimal currencyAmount)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"UPDATE orgt_TourGroup SET SaleTotalCost={0},SaleTotalPrice={1},[Currency]='{2}',[ExchangeRate]={3},[CurrencyAmount]={4} WHERE TourgroupID='{5}'", saleTotalCost, saleTotalPrice,currency,exchangeRate,currencyAmount, tourGroupId);
            DbCommand dbCommand = db.GetSqlStringCommand(sql);

            return db.ExecuteNonQuery(dbCommand);

        }
        /// <summary>
        /// 根据团Id更新‘审核成本、审核价格’
        /// </summary>
        /// <param name="tourGroupId"></param>
        /// <returns></returns>
        public static int UpdateTourGroupBudge(string tourGroupId)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"UPDATE orgt_TourGroup SET BudgeTotalCost=(SELECT SUM(budget) FROM fee_expense WHERE TourgroupID='{0}'),
                                        BudgeTotalPrice=(SELECT SUM(PlanPrice) FROM fin_incomeItem WHERE TourgroupID='{0}') WHERE TourgroupID='{0}'", tourGroupId);
            DbCommand dbCommand = db.GetSqlStringCommand(sql);

            return db.ExecuteNonQuery(dbCommand);

        }

        /// <summary>
        /// 根据团Id更新‘实际成本、实际价格’
        /// </summary>
        /// <param name="tourGroupId"></param>
        /// <param name="realTotalCost"></param>
        /// <param name="realTotalPrice"></param>
        /// <returns></returns>
        public static int UpdateTourGroupReal(string tourGroupId, decimal realTotalCost, decimal realTotalPrice)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"UPDATE orgt_TourGroup SET RealTotalCost={0},RealTotalPrice={1} WHERE TourgroupID='{2}'", realTotalCost, realTotalPrice, tourGroupId);
            DbCommand dbCommand = db.GetSqlStringCommand(sql);

            return db.ExecuteNonQuery(dbCommand);

        }
        /// <summary>
        /// 根据团Id更新‘审核成本’
        /// </summary>
        /// <param name="tourGroupId"></param>
        /// <returns></returns>
        public static int UpdateTourGroupBudgeCost(string tourGroupId)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"UPDATE orgt_TourGroup SET BudgeTotalCost=(SELECT SUM(budget) FROM fee_expense WHERE TourgroupID='{0}' AND Status<>'{1}') WHERE TourgroupID='{0}'", tourGroupId, ExpenseStatusEnum.取消);
            DbCommand dbCommand = db.GetSqlStringCommand(sql);

            return db.ExecuteNonQuery(dbCommand);
        }

        /// <summary>
        /// 根据团Id更新‘实际成本’
        /// </summary>
        /// <param name="tourGroupId"></param>
        /// <returns></returns>
        public static int UpdateTourGroupRealCost(string tourGroupId)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"UPDATE orgt_TourGroup SET RealTotalCost=(SELECT SUM(ApplyPayTotal) FROM fee_expense WHERE TourgroupID='{0}'AND Status<>'{1}') WHERE TourgroupID='{0}'", tourGroupId, ExpenseStatusEnum.取消);
            DbCommand dbCommand = db.GetSqlStringCommand(sql);

            return db.ExecuteNonQuery(dbCommand);
        }
        public static int UpdateTourGroupRealCostbyGourpIDs(Dictionary<int, string> tourGroupIds)
        {

            Database db = DatabaseFactory.CreateDatabase();
            StringBuilder sql = new StringBuilder();
            foreach (KeyValuePair<int, string> item in tourGroupIds)
            {
                sql.AppendFormat(@"UPDATE orgt_TourGroup SET RealTotalCost=(SELECT SUM(ApplyPayTotal) FROM fee_expense WHERE TourgroupID='{0}'AND Status<>'{1}') WHERE TourgroupID='{0}'; ", item.Value, ExpenseStatusEnum.取消);
            }

            return db.ExecuteNonQuery(CommandType.Text, sql.ToString());

        }

        /// <summary>
        /// 根据团Id更新‘审核价格’
        /// </summary>
        /// <param name="tourGroupId"></param>
        /// <returns></returns>
        public static int UpdateTourGroupBudgePrice(string tourGroupId)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"UPDATE orgt_TourGroup SET BudgeTotalPrice=(SELECT SUM(PlanPrice) FROM fin_IncomeItem WHERE TourgroupID='{0}') WHERE TourgroupID='{0}'", tourGroupId);
            DbCommand dbCommand = db.GetSqlStringCommand(sql);

            return db.ExecuteNonQuery(dbCommand);
        }
        /// <summary>
        /// 根据团Id更新‘实际价格’
        /// </summary>
        /// <param name="tourGroupId"></param>
        /// <returns></returns>
        public static int UpdateTourGroupRealPrice(string tourGroupId)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"UPDATE orgt_TourGroup SET RealTotalPrice=(SELECT SUM(ReceivedPrice) FROM fin_IncomeItem WHERE TourgroupID='{0}') WHERE TourgroupID='{0}'", tourGroupId);
            DbCommand dbCommand = db.GetSqlStringCommand(sql);

            return db.ExecuteNonQuery(dbCommand);
        }
        /// <summary>
        /// 更新团HasGuide标志
        /// </summary>
        /// <param name="tourGroupId">团ID</param>
        /// <param name="hasGuide"></param>
        /// <returns></returns>
        public static int UpdateTourGroupHasGuide(string tourGroupId, bool hasGuide)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"UPDATE orgt_TourGroup SET HasGuide={0} WHERE TourgroupID='{1}'", hasGuide == true ? 1 : 0, tourGroupId);
            DbCommand dbCommand = db.GetSqlStringCommand(sql);

            return db.ExecuteNonQuery(dbCommand);

        }
        public static int UpdateTourGroup(TourGroupEntity tourGroupData)
        {
            Database db = DatabaseFactory.CreateDatabase();
//            string sql = string.Format(@"UPDATE orgt_TourGroup SET
//                                        [TourgroupName]='{0}',                               [TourgroupState]='{1}',
//                                        [AdultNum]={2},                                      [ChildNum]={3},
//                                        [StartDate]='{4}',                                   [EndDate]='{5}',
//                                        [OwnerA]='{6}',                                      [OwnerB]='{7}',
//                                        [SaleTotalCost]={8},                                 [SaleTotalPrice]={9},
//                                        [BudgeTotalCost]={10},                               [BudgeTotalPrice]={11},
//                                        [RealTotalCost]={12},                                [RealTotalPrice]={13},
//                                        [Guesttype]='{14}',                                  [Servicetype]='{15}',
//                                        [Tourgrouptype]='{16}',                              [Country]='{17}',
//                                        [CountryCode]='{18}',                                [CustomerName]='{19}',
//                                        [CustomerCode]='{20}',                               [StayOver]={21}, 
//                                        [AccompanyNum]={22},                                 [AccompanyNames]='{23}',
//                                        [HasGuide]={24},                                     [BuildGroupDate]='{25}',
//                                        [Remark]='{26}',                                     [LocalRemark]='{27}',
//                                        [Currency]='{28}',                                   [ExchangeRate]={29},
//                                        [CurrencyAmount]={30} 
//                                        WHERE TourgroupID='{31}'",
//                                        tourGroupData.TourgroupName,
//                                        tourGroupData.TourgroupState,
//                                        tourGroupData.AdultNum,
//                                        tourGroupData.ChildNum,
//                                        tourGroupData.StartDate,
//                                        tourGroupData.EndDate,
//                                        tourGroupData.OwnerA,
//                                        tourGroupData.OwnerB,
//                                        tourGroupData.SaleTotalCost,
//                                        tourGroupData.SaleTotalPrice,
//                                        tourGroupData.BudgeTotalCost,
//                                        tourGroupData.BudgeTotalPrice,
//                                        tourGroupData.RealTotalCost,
//                                        tourGroupData.RealTotalPrice,
//                                        tourGroupData.Guesttype,
//                                        tourGroupData.Servicetype,
//                                        tourGroupData.Tourgrouptype,
//                                        tourGroupData.Country,
//                                        tourGroupData.CountryCode,
//                                        tourGroupData.CustomerName,
//                                        tourGroupData.CustomerCode,
//                                        tourGroupData.StayOver,
//                                        tourGroupData.AccompanyNum,
//                                        tourGroupData.AccompanyNames,
//                                        tourGroupData.HasGuide,
//                                        tourGroupData.BuildGroupDate,
//                                        tourGroupData.Remark,
//                                        tourGroupData.LocalRemark,
//                                        tourGroupData.Currency,
//                                        tourGroupData.ExchangeRate,
//                                        tourGroupData.CurrencyAmount,
//                                        tourGroupData.TourgroupID
            string sql = string.Format(@"UPDATE orgt_TourGroup SET 
                                        [TourgroupName]=@TourgroupName,                     [TourgroupState]=@TourgroupState,
                                        [AdultNum]=@AdultNum,                               [ChildNum]=@ChildNum,
                                        [StartDate]=@StartDate,                             [EndDate]=@EndDate,
                                        [OwnerA]=@OwnerA,                                   [OwnerB]=@OwnerB,
                                        [SaleCost]=@SaleCost,                               [SalePrice]=@SalePrice,
                                        [SaleTotalCost]=@SaleTotalCost,                     [SaleTotalPrice]=@SaleTotalPrice,
                                        [BudgeTotalCost]=@BudgeTotalCost,                   [BudgeTotalPrice]=@BudgeTotalPrice,
                                        [RealTotalCost]=@RealTotalCost,                     [RealTotalPrice]=@RealTotalPrice,
                                        [Guesttype]=@Guesttype,                             [Servicetype]=@Servicetype,
                                        [Tourgrouptype]=@Tourgrouptype,                     [Country]=@Country,
                                        [CountryCode]=@CountryCode,                         [CustomerName]=@CustomerName,
                                        [CustomerCode]=@CustomerCode,                       [StayOver]=@StayOver, 
                                        [AccompanyNum]=@AccompanyNum,                       
                                        [HasGuide]=@HasGuide,                               [BuildGroupDate]=@BuildGroupDate,
                                        [Remark]=@Remark,                                   
                                        [Currency]=@Currency,                               [ExchangeRate]=@ExchangeRate,
                                        [CurrencyAmount]=@CurrencyAmount,                   [PriceMethod]=@PriceMethod
                                        WHERE TourgroupID=@TourgroupID");
            DbCommand dbCommand = db.GetSqlStringCommand(sql);

            db.AddInParameter(dbCommand, "TourgroupName", DbType.String, tourGroupData.TourgroupName);
            db.AddInParameter(dbCommand, "TourgroupState", DbType.String, tourGroupData.TourgroupState);
            db.AddInParameter(dbCommand, "AdultNum", DbType.Int32, tourGroupData.AdultNum);
            db.AddInParameter(dbCommand, "ChildNum", DbType.Int32, tourGroupData.ChildNum);
            db.AddInParameter(dbCommand, "StartDate", DbType.String, tourGroupData.StartDate);
            db.AddInParameter(dbCommand, "EndDate", DbType.String, tourGroupData.EndDate);
            db.AddInParameter(dbCommand, "OwnerA", DbType.String, tourGroupData.OwnerA);
            db.AddInParameter(dbCommand, "OwnerB", DbType.String, tourGroupData.OwnerB);
            db.AddInParameter(dbCommand, "SaleCost", DbType.Decimal, tourGroupData.SaleCost);
            db.AddInParameter(dbCommand, "SalePrice", DbType.Decimal, tourGroupData.SalePrice);
            db.AddInParameter(dbCommand, "SaleTotalCost", DbType.Decimal, tourGroupData.SaleTotalCost);
            db.AddInParameter(dbCommand, "SaleTotalPrice", DbType.Decimal, tourGroupData.SaleTotalPrice);
            db.AddInParameter(dbCommand, "BudgeTotalCost", DbType.Decimal, tourGroupData.BudgeTotalCost);
            db.AddInParameter(dbCommand, "BudgeTotalPrice", DbType.Decimal, tourGroupData.BudgeTotalPrice);
            db.AddInParameter(dbCommand, "RealTotalCost", DbType.Decimal, tourGroupData.RealTotalCost);
            db.AddInParameter(dbCommand, "RealTotalPrice", DbType.Decimal, tourGroupData.RealTotalPrice);
            db.AddInParameter(dbCommand, "Guesttype", DbType.String, tourGroupData.Guesttype);
            db.AddInParameter(dbCommand, "Servicetype", DbType.String, tourGroupData.Servicetype);
            db.AddInParameter(dbCommand, "Tourgrouptype", DbType.String, tourGroupData.Tourgrouptype);
            db.AddInParameter(dbCommand, "Country", DbType.String, tourGroupData.Country);
            db.AddInParameter(dbCommand, "CountryCode", DbType.String, tourGroupData.CountryCode);
            db.AddInParameter(dbCommand, "CustomerName", DbType.String, tourGroupData.CustomerName);
            db.AddInParameter(dbCommand, "CustomerCode", DbType.String, tourGroupData.CustomerCode);
            db.AddInParameter(dbCommand, "StayOver", DbType.Int32, tourGroupData.StayOver);
            db.AddInParameter(dbCommand, "AccompanyNum", DbType.Int32, tourGroupData.AccompanyNum);
            //db.AddInParameter(dbCommand, "AccompanyNames", DbType.String, tourGroupData.AccompanyNames);
            db.AddInParameter(dbCommand, "HasGuide", DbType.Int32, tourGroupData.HasGuide);
            db.AddInParameter(dbCommand, "BuildGroupDate", DbType.String, tourGroupData.BuildGroupDate);
            db.AddInParameter(dbCommand, "Remark", DbType.String, tourGroupData.Remark);
            //db.AddInParameter(dbCommand, "LocalRemark", DbType.String, tourGroupData.LocalRemark);
            db.AddInParameter(dbCommand, "Currency", DbType.String, tourGroupData.Currency);
            db.AddInParameter(dbCommand, "ExchangeRate", DbType.Decimal, tourGroupData.ExchangeRate);
            db.AddInParameter(dbCommand, "CurrencyAmount", DbType.Decimal, tourGroupData.CurrencyAmount);
            db.AddInParameter(dbCommand, "PriceMethod", DbType.String, tourGroupData.PriceMethod);
            db.AddInParameter(dbCommand, "TourgroupID", DbType.String, tourGroupData.TourgroupID);

            return db.ExecuteNonQuery(dbCommand);
        }
        internal static int UpdateTourGroupStartAndEndDate(string tourGroupId, DateTime newStartDate, int newAndOldDifferDays)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"UPDATE orgt_TourGroup SET [StartDate]='{0}',[EndDate]=CONVERT(char(10),dateadd(dd,{1},[EndDate]),120) 
                                         WHERE TourgroupID='{2}'", newStartDate.ToString("yyyy-MM-dd"), newAndOldDifferDays, tourGroupId);
            return db.ExecuteNonQuery(CommandType.Text, sql);
        }

        #endregion

        #region 行程计划数据的获取
        public static DataSet GetTourGroupSchedule(string  tourGroupID)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format("SELECT [TourGroupScheduleID], [TourgroupID], [Date], [Time], [StayOver], [Journeytype], [StartCity], [DestinationCity], [TrafficType], [Traffic], [TicketType], [TravelAgencyID],[TravelAgencyName], [Reception], [Context], [Dining], [Hotel],[HotelID],[Seat],[ScheduleMethod] FROM orgt_TourGroupSchedule WHERE TourgroupID='{0}'ORDER BY [Date], [Time]", tourGroupID);
            
            return db.ExecuteDataSet(CommandType.Text, sql);
        }
        /// <summary>
        /// 为旅行社评价获取团行程计划中的相关信息
        /// </summary>
        /// <param name="tourGroupID"></param>
        /// <returns></returns>
        public static DataSet GetTourGroupScheduleForFeedback(string tourGroupID)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format("SELECT [DestinationCity],[TravelAgencyID],[TravelAgencyName],[HotelID],[Hotel] FROM orgt_TourGroupSchedule WHERE TourgroupID='{0}' AND (Journeytype='{1}' OR Journeytype='{2}')", tourGroupID, JourneyTypeEnum.抵, JourneyTypeEnum.入境);

            return db.ExecuteDataSet(CommandType.Text, sql);
        }

        /// <summary>
        /// 根据指定团号获取其第一调计划号
        /// </summary>
        /// <param name="p"></param>
        /// <returns></returns>
        internal static string GetFirstScheduleIDForTourGroupID(string tourGroupID)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format("SELECT TOP 1 TourGroupScheduleID FROM orgt_TourGroupSchedule WHERE TourgroupID='{0}'", tourGroupID);

            object obj = db.ExecuteScalar(CommandType.Text,sql);
            if (obj == null)
                return "";
            else
                return obj.ToString();
        }
        /// <summary>
        /// 获取指定团计划的第一和最后一条的日期和时间
        /// </summary>
        /// <param name="tourGroupID"></param>
        /// <returns></returns>
        internal static string[,] GetScheduleDateTimeForTourGroupID(string tourGroupID)
        {
            string[,] data = new string[2,2];
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format("SELECT TOP 1 [Date],[Time] FROM orgt_TourGroupSchedule WHERE TourgroupID='{0}'  ORDER By [Date]", tourGroupID);

            IDataReader reader = db.ExecuteReader(CommandType.Text, sql);
            if (reader.Read())
            {
                data[0, 0] = reader["Date"].ToString();
                data[0, 1] = reader["Time"].ToString();
            }
            reader.Close();
            sql = string.Format("SELECT TOP 1 [Date],[Time] FROM orgt_TourGroupSchedule WHERE TourgroupID='{0}'  ORDER By [Date] DESC", tourGroupID);
            IDataReader reader1 = db.ExecuteReader(CommandType.Text, sql);
            if (reader1.Read())
            {
                data[1, 0] = reader1["Date"].ToString();
                data[1, 1] = reader1["Time"].ToString();
            }
            reader.Close();
            return data;
        }
        #endregion

        #region 行程计划打印——团、计划、旅行社信息获取

        public static DataTable GetTourGroupScheduleForSchedulePrint(string tourGroupID)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format("SELECT [Date], [Time], [Journeytype], [StartCity], [DestinationCity], [TrafficType], [Traffic], [Context],[Hotel],[Dining],[TicketType],[Seat],[ScheduleMethod] FROM orgt_TourGroupSchedule WHERE TourgroupID='{0}'order by [Date],[Time]", tourGroupID);
            DbCommand dbCommand = db.GetSqlStringCommand(sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }

        public static DataTable GetTourGroupScheduleForChangeNotifyPrint(string tourGroupID)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format("SELECT [Date], [Time], [Journeytype], [StartCity], [DestinationCity], [TrafficType], [Traffic],[Hotel] FROM orgt_TourGroupSchedule WHERE TourgroupID='{0}'and ([Journeytype]='入境' or [Journeytype]='抵' or [Journeytype]='离境' or([Journeytype]='离'and [TrafficType]='火车') ) order by [Date],[Time]", tourGroupID);
            DbCommand dbCommand = db.GetSqlStringCommand(sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }

        public static DataTable GetTeamGroupDataForSchedulePrint(string tourGroupID)
        {

            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
            string sql = "SELECT orgt_tourgroup.TourgroupID,orgt_tourgroup.TourgroupName,orgt_tourgroup.OwnerA,orgt_tourgroup.OwnerB,orgt_tourgroup.AdultNum,orgt_tourgroup.ChildNum,orgt_tourgroup.AccompanyNum,orgt_tourgroup.Remark,orgt_tourgroup.LocalRemark,orgt_tourgroup.Servicetype,orgt_tourgroup.Country,aspnet_UserInfo.Telephone,aspnet_UserInfo.Fax FROM orgt_tourgroup,aspnet_UserInfo WHERE TourGroupID = @tourGroupID and orgt_tourgroup.OwnerB = aspnet_UserInfo.[Name]";
            dbCommand = db.GetSqlStringCommand(sql);

            db.AddParameter(dbCommand, "@tourGroupID", DbType.String, ParameterDirection.Input, "", DataRowVersion.Default, tourGroupID);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;

        }
        /// <summary>
        /// 按指定ID顺序返回供应商信息，用union all语句完成
        /// </summary>
        /// <param name="providerIds"></param>
        /// <returns></returns>
        internal static DataTable GetTravelOrHotelInfoForPrint(string[] providerIds)
        {
            if (providerIds.Length == 0)
                return new DataTable();//返回一个空的数据表

            Database db = DatabaseFactory.CreateDatabase();

            StringBuilder formatSql = new StringBuilder();

            for (int i = 0; i < providerIds.Length; i++)
            {
                if (providerIds[i] == "") continue;

                formatSql.AppendFormat(@" SELECT  [ProviderName], [Contact], [Telephone], [fax]  FROM bas_Provider WHERE ProviderID={0} ", providerIds[i]);

                if (i != providerIds.Length - 1 && providerIds[i+1]!="")
                    formatSql.Append(" UNION ALL ");
            }
            DbCommand dbCommand = db.GetSqlStringCommand(formatSql.ToString());

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
            {
                return ds.Tables[0];
            }
            else
                return null;
        }

        internal static DataTable GetEntertainmenRestaurantInfoForPrint(string tourGroupID)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string formatSql = string.Format("SELECT distinct bas_Provider.[ProviderName], bas_Provider.[Contact], bas_Provider.[Telephone], bas_Provider.[fax]  FROM bas_Provider,fee_EntertainmentOrder WHERE fee_EntertainmentOrder.TourgroupID='{0}' and bas_Provider.ProviderID=fee_EntertainmentOrder.ProviderID and (bas_Provider.ProviderType like '%{1}%' or bas_Provider.ProviderType like '%{2}%')", tourGroupID, ProviderTypeEnum.餐厅, ProviderTypeEnum.演出);

            DbCommand dbCommand = db.GetSqlStringCommand(formatSql.ToString());

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
            {
                return ds.Tables[0];
            }
            else
                return null;
        }
        //public static DataTable GetScheduleTravelsAndHotelIDForPrint(string tourGroupID)
        //{
        //    Database db = DatabaseFactory.CreateDatabase();
        //    string formatSql = string.Format("SELECT [TravelAgencyID],[HotelID] FROM orgt_TourGroupSchedule WHERE TourgroupID='{0}'", tourGroupID);

        //    DbCommand dbCommand = db.GetSqlStringCommand(formatSql.ToString());
            
        //    DataSet ds = db.ExecuteDataSet(dbCommand);
        //    if (ds.Tables.Count > 0)
        //    {
        //        return ds.Tables[0];
        //    }
        //    else
        //        return null;
        //}

        public static DataTable GetScheduleTravelsIDForPrint(string tourGroupID)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string formatSql = string.Format("select TravelAgencyID from (select min([date]) as indexfield, TravelAgencyID from orgt_TourGroupSchedule WHERE TourgroupID = '{0}' group by TravelAgencyID) as a order by a.indexfield", tourGroupID);

            DbCommand dbCommand = db.GetSqlStringCommand(formatSql.ToString());

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
            {
                return ds.Tables[0];
            }
            else
                return null;
        }

        public static DataTable GetScheduleHotelIDForPrint(string tourGroupID)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string formatSql = string.Format("select HotelID from (select min([date]) as indexfield, HotelID from orgt_TourGroupSchedule WHERE TourgroupID = '{0}' and ScheduleMethod = '{1}'group by HotelID) as a order by a.indexfield", tourGroupID, HotelScheduleMethodEnum.自订);

            DbCommand dbCommand = db.GetSqlStringCommand(formatSql.ToString());

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
            {
                return ds.Tables[0];
            }
            else
                return null;
        }

        public static DataTable GetHotelForReception(string tourGroupID)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string formatSql = string.Format("select p.City,h.ArrivalTime,h.LeaveTime,h.ProviderName,p.Address,p.Telephone from fee_HotelOrder h,bas_Provider p WHERE h.TourgroupID = '{0}' and h.ProviderID = p.ProviderID and h.ScheduleMethod <> '{1}' order by h.ArrivalTime", tourGroupID, HotelScheduleMethodEnum.确认);

            DbCommand dbCommand = db.GetSqlStringCommand(formatSql.ToString());

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
            {
                return ds.Tables[0];
            }
            else
                return null;
        }


        //完成酒店及酒店相关信息获取，做为Voucher内容打印
        public static DataTable GetScheduleHotelOrderForPrint(string tourGroupID)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string formatSql = string.Format("SELECT h.*,t.TravelAgencyID,t.TravelAgencyName,t.Hotel,t.HotelID,t.Reception,t.DestinationCity,t.ScheduleMethod,t.TourgroupID FROM fee_hotelorder h ,orgt_TourGroupSchedule t WHERE h.TourGroupScheduleID = t.TourGroupScheduleID and h.TourgroupID = '{0}' order by ArrivalTime", tourGroupID);

            DbCommand dbCommand = db.GetSqlStringCommand(formatSql.ToString());

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
            {
                return ds.Tables[0];
            }
            else
                return null;
        }

        #endregion

        #region 获取客人列表

        internal static DataTable GetGuestListData(string tourGroupId)
        {
            Database db = DatabaseFactory.CreateDatabase();

            string Sql = string.Format("SELECT * FROM orgt_GuestList WHERE  TourgroupID ='{0}' ORDER BY OrderID", tourGroupId);

            DbCommand dbCommand = db.GetSqlStringCommand(Sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        public static DataTable GetTeamGroupDataForGuestListPrint(string tourGroupID)
        {

            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
            string sql = "SELECT TourgroupID,TourgroupName FROM orgt_tourgroup WHERE TourGroupID = @tourGroupID";
            dbCommand = db.GetSqlStringCommand(sql);

            db.AddParameter(dbCommand, "@tourGroupID", DbType.String, ParameterDirection.Input, "", DataRowVersion.Default, tourGroupID);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;

        }
        #endregion

        #region 客人列表的增、删、改

        internal static string CreateGuestListData(GuestListEntity entity)
        {
            Database db = DatabaseFactory.CreateDatabase();

            string sql = string.Format(@"INSERT INTO orgt_GuestList([OrderID], [TourgroupID], [TourgroupName], [IsVIP], [IsCancel], [Nationality],
                                                                    [Name], [Gender], [Remark], [Room], [IdCard], [Birthday], [IsTickets],[Job],[VisaPeriod],[PassportPeriod],[PassportAddress])
                                                                    VALUES({0},'{1}','{2}',{3},{4},'{5}','{6}','{7}','{8}','{9}','{10}','{11}',{12},'{13}','{14}','{15}','{16}');Select SCOPE_IDENTITY();",
                                                                      entity.OrderID,
                                                                      entity.TourgroupID,
                                                                      entity.TourgroupName,
                                                                      entity.IsVIP,
                                                                      entity.IsCancel,
                                                                      entity.Nationality,
                                                                      entity.Name,
                                                                      entity.Gender,
                                                                      entity.Remark,
                                                                      entity.Room,
                                                                      entity.IdCard,
                                                                      entity.Birthday,
                                                                      entity.IsTickets,
                                                                      entity.Job,
                                                                      entity.VisaPeriod,
                                                                      entity.PassportPeriod,
                                                                      entity.PassportAddress);

            DbCommand dbCommand = db.GetSqlStringCommand(sql);

            return db.ExecuteScalar(dbCommand).ToString();
        }

        internal static int UpdateGuestListData(GuestListEntity entity)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"UPDATE orgt_GuestList SET [OrderID]=@OrderID, [TourgroupID]=@TourgroupID, [TourgroupName]=@TourgroupName, [IsVIP]=@IsVIP, [IsCancel]=@IsCancel, 
                                                                    [Nationality]=@Nationality, [Name]=@Name, [Gender]=@Gender, [Remark]=@Remark, [Room]=@Room,
                                                                    [IdCard]=@IdCard, [Birthday]=@Birthday, [IsTickets]=@IsTickets,[Job]=@Job,[VisaPeriod]=@VisaPeriod,[PassportPeriod]=@PassportPeriod,[PassportAddress]=@PassportAddress 
                                                                    WHERE GuestListID=@GuestListID");

            DbCommand dbCommand = db.GetSqlStringCommand(sql);
            db.AddInParameter(dbCommand, "OrderID", DbType.Int32, entity.OrderID);
            db.AddInParameter(dbCommand, "TourgroupID", DbType.String, entity.TourgroupID);
            db.AddInParameter(dbCommand, "TourgroupName", DbType.String, entity.TourgroupName);
            db.AddInParameter(dbCommand, "IsVIP", DbType.Int32, entity.IsVIP);
            db.AddInParameter(dbCommand, "IsCancel", DbType.Int32, entity.IsCancel);
            db.AddInParameter(dbCommand, "Nationality", DbType.String, entity.Nationality);
            db.AddInParameter(dbCommand, "Name", DbType.String, entity.Name);
            db.AddInParameter(dbCommand, "Gender", DbType.String, entity.Gender);
            db.AddInParameter(dbCommand, "Remark", DbType.String, entity.Remark);
            db.AddInParameter(dbCommand, "Room", DbType.String, entity.Room);
            db.AddInParameter(dbCommand, "IdCard", DbType.String, entity.IdCard);
            db.AddInParameter(dbCommand, "Birthday", DbType.String, entity.Birthday);
            db.AddInParameter(dbCommand, "IsTickets", DbType.Int32, entity.IsTickets);
            db.AddInParameter(dbCommand, "Job", DbType.String, entity.Job);
            db.AddInParameter(dbCommand, "VisaPeriod", DbType.String, entity.VisaPeriod);
            db.AddInParameter(dbCommand, "PassportPeriod", DbType.String, entity.PassportPeriod);
            db.AddInParameter(dbCommand, "PassportAddress", DbType.String, entity.PassportAddress);
            db.AddInParameter(dbCommand, "GuestListID", DbType.Int32, entity.GuestListID);

            return db.ExecuteNonQuery(dbCommand);

        }
        internal static int UpdateGuestListOrder(GuestListEntity entity)
        {

            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"SELECT OrderID FROM orgt_GuestList WHERE TourgroupID = '{0}' and OrderID = '{1}'",entity.TourgroupID, entity.OrderID);

            DbCommand dbCommand = db.GetSqlStringCommand(sql.ToString());

            DataSet ds = db.ExecuteDataSet(dbCommand);

            if (ds.Tables[0].Rows.Count < 2) return 0;//无重复顺序
           
            sql = string.Format(@"UPDATE orgt_GuestList SET [OrderID]=[OrderID]+1 WHERE (TourgroupID = '{0}' and OrderID > '{1}') or GuestListID = '{2}'",
                                  entity.TourgroupID, entity.OrderID, entity.GuestListID);//有重复顺序，大于此顺序的加1，本身加1

            dbCommand = db.GetSqlStringCommand(sql);

            return db.ExecuteNonQuery(dbCommand);
        }

        internal static int DeleteGuestListData(string guestListId)
        {
            if (guestListId == "") return 0;

            Database db = DatabaseFactory.CreateDatabase();

            string sql = string.Format(@"DELETE FROM orgt_GuestList WHERE GuestListID={0}", guestListId);

            return db.ExecuteNonQuery(CommandType.Text, sql);
        }

        #endregion

        #region 行程计划有关数据增、删、改

        internal static int CopyTourGroupSchedule(string newTourGroupId,string oldTourGroupId)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"INSERT INTO orgt_TourGroupSchedule([TourgroupID], [Date], [Time], [StayOver], [Journeytype], [StartCity], [DestinationCity], [TrafficType], [Traffic], [TicketType], [TravelAgencyName], [Reception], [Context], [Dining], [Hotel],[TravelAgencyID],[HotelID],[Seat]) 
                                         SELECT 
                                            '{0}', [Date], [Time], [StayOver], [Journeytype], [StartCity], [DestinationCity], [TrafficType], [Traffic], [TicketType], [TravelAgencyName], [Reception], [Context], [Dining], [Hotel],[TravelAgencyID],[HotelID],[Seat]  
                                         FROM orgt_TourGroupSchedule WHERE TourgroupID='{1}'", newTourGroupId, oldTourGroupId);
            return db.ExecuteNonQuery(CommandType.Text, sql);
        }
        
        /// <summary>
        /// 行程计划数据创建
        /// </summary>
        /// <param name="scheduleData"></param>
        /// <returns></returns>
        public static int CreateTourGroupSchedule(TourGroupScheduleEntity scheduleData)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"INSERT INTO orgt_TourGroupSchedule([TourgroupID], [Date], [Time], [StayOver], [Journeytype], [StartCity], [DestinationCity], [TrafficType], [Traffic], [TicketType], [TravelAgencyName], [Reception], [Context], [Dining], [Hotel],[TravelAgencyID],[HotelID],[Seat]) VALUES('{0}', '{1}', '{2}', {3}, '{4}','{5}','{6}', '{7}', '{8}','{9}','{10}','{11}','{12}','{13}','{14}',{15},{16},'{17}')",
                scheduleData.TourgroupID,
                scheduleData.Date,
                scheduleData.Time,
                scheduleData.StayOver,
                scheduleData.Journeytype,
                scheduleData.StartCity,
                scheduleData.DestinationCity,
                scheduleData.TrafficType,
                scheduleData.Traffic,
                scheduleData.TicketType,
                scheduleData.TravelAgencyName,
                scheduleData.Reception,
                scheduleData.Context,
                scheduleData.Dining,
                scheduleData.Hotel,
                scheduleData.TravelAgencyID,
                scheduleData.HotelID,
                scheduleData.Seat
                );
            return db.ExecuteNonQuery(CommandType.Text, sql);
        }

        /// <summary>
        /// 根据行程计划号，更新行程计划数据
        /// </summary>
        /// <param name="scheduleData"></param>
        /// <returns></returns>
        public static int UpdateTourGroupSchedule(TourGroupScheduleEntity scheduleData)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"UPDATE orgt_TourGroupSchedule SET
                                        [TourgroupID]='{0}',
                                        [Date]='{1}',                                      [Time]='{2}',
                                        [StayOver]={3},                                    [Journeytype]='{4}',
                                        [StartCity]='{5}',                                 [DestinationCity]='{6}',
                                        [TrafficType]='{7}',                               [Traffic]='{8}',
                                        [TicketType]='{9}',                                [TravelAgencyName]='{10}',
                                        [Reception]='{11}',                                [Context]='{12}',
                                        [Dining]='{13}',                                   [Hotel]='{14}',
                                        [TravelAgencyID]={15},                             [HotelID]={16},
                                        [Seat]='{17}',                                     [ScheduleMethod]='{18}' 
                                        WHERE TourGroupScheduleID={19}",
                                        scheduleData.TourgroupID,
                                        scheduleData.Date,
                                        scheduleData.Time,
                                        scheduleData.StayOver,
                                        scheduleData.Journeytype,
                                        scheduleData.StartCity,
                                        scheduleData.DestinationCity,
                                        scheduleData.TrafficType,
                                        scheduleData.Traffic,
                                        scheduleData.TicketType,
                                        scheduleData.TravelAgencyName,
                                        scheduleData.Reception,
                                        scheduleData.Context,
                                        scheduleData.Dining,
                                        scheduleData.Hotel,
                                        scheduleData.TravelAgencyID,
                                        scheduleData.HotelID,
                                        scheduleData.Seat,
                                        scheduleData.ScheduleMethod,
                                        scheduleData.TourGroupScheduleID
                                    );
            return db.ExecuteNonQuery(CommandType.Text, sql);
        }
        /// <summary>
        /// 根据团号和起始日期延期长度，修改计划日期数
        /// </summary>
        /// <param name="tourGroupId"></param>
        /// <param name="newAndOldDifferDays"></param>
        internal static int UpdateTourGroupScheduleDate(string tourGroupId, int newAndOldDifferDays)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"UPDATE orgt_TourGroupSchedule SET [Date]=CONVERT(char(10),dateadd(dd,{0},[Date]),120) WHERE TourGroupID='{1}'", newAndOldDifferDays, tourGroupId);
            return db.ExecuteNonQuery(CommandType.Text, sql);
        }

        /// <summary>
        /// 删除指定计划号数据
        /// </summary>
        /// <param name="tourGroupScheduleID"></param>
        /// <returns></returns>
        public static int DeleteTourGroupSchedule(int tourGroupScheduleID)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"DELETE FROM orgt_TourGroupSchedule WHERE TourGroupScheduleID={0}",tourGroupScheduleID);
            return db.ExecuteNonQuery(CommandType.Text, sql);
        }
        /// <summary>
        /// 删除指定团号的形成计划数据
        /// </summary>
        /// <param name="tourGroupID"></param>
        /// <returns></returns>
        public static int DeleteTourGroupSchedule(string tourGroupID)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"DELETE FROM orgt_TourGroupSchedule WHERE TourGroupID='{0}'",tourGroupID);
            return db.ExecuteNonQuery(CommandType.Text, sql);
        }
        #endregion

        #region 销售报价数据处理

        public static DataSet GetSaleDataSet(string tourgroupid)
        {
            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
            //DataSet ds;
            string sql = string.Format(@"   SELECT [dayID], [CityName], [ComplexCost], [MealCost], [OverKMCost], [RoomCost], [TicketsCost], [CitiesTrafficCost] FROM [orgt_SaleQuotation] WHERE tourgroupid='{0}';
                                            SELECT * FROM [orgt_SaleQuotationCalc]  WHERE tourgroupid='{0}'", tourgroupid);
            dbCommand = db.GetSqlStringCommand(sql);

            return db.ExecuteDataSet(dbCommand);

        }
        
        /// <summary>
        /// 根据团号判断是否存在导入的销售数据
        /// </summary>
        /// <param name="tourgroupid"></param>
        /// <returns></returns>
        private static bool GetSaleQuotationForTourgroupID(string tourgroupid)
        {
            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
            string sql = string.Format("SELECT TourgroupID FROM orgt_SaleQuotation where tourgroupid='{0}'", tourgroupid);
            dbCommand = db.GetSqlStringCommand(sql);

            object obj = db.ExecuteScalar(dbCommand);
            if (obj == null)
                return false;
            else
                return true;
        }
        /// <summary>
        /// 删除指定团号的销售报价数据
        /// </summary>
        /// <param name="tourGroupId">团号</param>
        /// <returns></returns>
        public static int DeleteSaleQuotationData(string tourGroupId)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"DELETE FROM orgt_SaleQuotation WHERE TourgroupID='{0}'; DELETE FROM orgt_SaleQuotationCalc WHERE TourgroupID='{1}'", tourGroupId, tourGroupId);
            return db.ExecuteNonQuery(CommandType.Text, sql);
        }
        /// <summary>
        /// 检测系统中是否已经存在此报价信息
        /// </summary>
        /// <param name="filename">销售报价文件名</param>
        /// <returns>true已经存在,false不存在</returns>
        public static int LoadPriceExistChecked(string filename, string selectedTourgroupId)
        {
            try
            {
                DataSet teamPriceDs = new DataSet();
                teamPriceDs.ReadXml(filename, XmlReadMode.ReadSchema);

                if (teamPriceDs.Tables.Count == 2 && teamPriceDs.Tables[0].TableName == "teamPriceDt" && teamPriceDs.Tables[1].TableName == "teamAverageDt")
                {
                    DataTable dt = teamPriceDs.Tables["teamAverageDt"];

                    string tourgroupId = dt.Rows[0]["TourgroupID"].ToString();

                    TeamStateEnum tourgroupState = TeamGroupCtrl.GetTourGroupState(tourgroupId);
                    switch (tourgroupState)
                    {
                        case TeamStateEnum.计划:
                        case TeamStateEnum.成行:
                            break;
                        case TeamStateEnum.全部:
                        case TeamStateEnum.审核:
                        case TeamStateEnum.终结:
                        case TeamStateEnum.删除:
                            return -5;
                        default:
                            return -5;
                    }

                    if (selectedTourgroupId.Trim()!=tourgroupId.Trim())
                        return 0;//当前导入的销售报价数据与选择的团不符

                    if (GetSaleQuotationForTourgroupID(tourgroupId) == true)
                        return -1;//已经导入过此数据
                    else
                        return -2;//没有导入过此数据
                }
                return -3;//数据非法
            }
            catch
            {
                return -4;//导入文件非法
            }
        }
        /// <summary>
        /// 导入销售报价数据
        /// </summary>
        /// <param name="filename">销售报价文件</param>
        /// <param name="customerName">导入报价团的客户名称</param>
        /// <returns></returns>
        public static bool LoadPriceDataFile(string filename, string customerName)
        {
            try
            {
                DataSet teamPriceDs = new DataSet();
                teamPriceDs.ReadXml(filename, XmlReadMode.ReadSchema);

                if (teamPriceDs.Tables.Count == 2 && teamPriceDs.Tables[0].TableName == "teamPriceDt" && teamPriceDs.Tables[1].TableName == "teamAverageDt")
                {
                    DataTable dtTeamPrice = teamPriceDs.Tables["teamPriceDt"];
                    DataTable dtTeamAverage = teamPriceDs.Tables["teamAverageDt"];

                    //clear data
                    string tourgroupId = dtTeamAverage.Rows[0]["TourgroupID"].ToString();
                    DeleteSaleQuotationData(tourgroupId);
                    if (dtTeamPrice.Rows.Count > 0)
                    {
                        StringBuilder Sql = new StringBuilder();
                        foreach (DataRow dr in dtTeamPrice.Rows)
                        {
                            //orgt_SaleQuotation表
                            Sql.AppendFormat(@"INSERT INTO orgt_SaleQuotation ([TourgroupID], [dayID], [CityName], [ComplexCost], [MealCost], [OverKMCost], [RoomCost], [TicketsCost], [CitiesTrafficCost])
                                              VALUES('{0}',{1},'{2}',{3},{4},{5}, {6},{7},{8});",
                                                                                               tourgroupId,
                                                                                               dr["dayID"].ToString(),
                                                                                               dr["CityName"].ToString(),
                                                                                               dr["ComplexCost"].ToString(),
                                                                                               dr["MealCost"].ToString(),
                                                                                               dr["OverKMCost"].ToString(),
                                                                                               dr["RoomCost"].ToString(),
                                                                                               dr["TicketsCost"].ToString(),
                                                                                               dr["CitiesTrafficCost"].ToString());
                        }
                        //orgt_SaleQuotationCalc表
                        Sql.AppendFormat(@"INSERT INTO orgt_SaleQuotationCalc([TourgroupID], [Population], [AverageCost], [ProfitRate], [AveragePrice],[Currency],[ExchangeRate],[CurrencyAmount])
                                           VALUES('{0}', {1},{2}, {3},{4},'{5}',{6},{7})",
                                           tourgroupId,
                                           dtTeamAverage.Rows[0]["Population"].ToString(),
                                           dtTeamAverage.Rows[0]["AverageCost"].ToString(),
                                           dtTeamAverage.Rows[0]["ProfitRate"].ToString(),
                                           dtTeamAverage.Rows[0]["AveragePrice"].ToString(),
                                           dtTeamAverage.Rows[0]["Currency"].ToString(),
                                           dtTeamAverage.Rows[0]["ExchangeRate"].ToString(),
                                           dtTeamAverage.Rows[0]["CurrencyAmount"].ToString());
                        Database db = DatabaseFactory.CreateDatabase();
                        db.ExecuteNonQuery(CommandType.Text, Sql.ToString());

                        decimal population = decimal.Parse(dtTeamAverage.Rows[0]["Population"].ToString());
                        decimal averageCost = decimal.Parse(dtTeamAverage.Rows[0]["AverageCost"].ToString());
                        decimal averagePrice = decimal.Parse(dtTeamAverage.Rows[0]["AveragePrice"].ToString());
                        string currency = dtTeamAverage.Rows[0]["Currency"].ToString();
                        decimal exchangeRate = decimal.Parse(dtTeamAverage.Rows[0]["ExchangeRate"].ToString());
                        decimal currencyAmount = decimal.Parse(dtTeamAverage.Rows[0]["CurrencyAmount"].ToString());
                        //更新團表中的“预算成本、预算价格”
                        UpdateTourGroupSale(tourgroupId, averageCost * population, averagePrice * population, currency,exchangeRate,currencyAmount);
                        
                    }
                    return true;//导入成功！
                }
                else
                    return false;//打开的文件数据非法，请找到正确的数据文件重新加载
            }
            catch
            {
                return false;
            }
        }
        #endregion


    }
}
